﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;
using System.Data.SqlClient;
using System.Data;

namespace SD.DAL
{
    public class TeamDAO : BaseDAO, ITeamDAO
    {
        /// <summary>
        /// Get Team by Filter
        /// </summary>
        /// <param name="filterExpression"></param>
        /// <param name="includeInactive"></param>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public List<Team> GetByFilter(string filterExpression, bool includeInactive, string departId)
        {
            // Create a command object
            SqlCommand comm = CreateCommand();
            // Call command by store
            comm.CommandText = "TeamGetByFilter";
            // Add parammeter to store procedure
            comm.Parameters.AddWithValue("@filterExpression", filterExpression);
            comm.Parameters.AddWithValue("@includeInactive", includeInactive);
            comm.Parameters.AddWithValue("@DepartmentID", departId);

            // Create a data table
            DataTable dt = ExecuteSelectCommand(comm);
            // Get list of directorate
            List<Team> list = new List<Team>();
            Team team = null;
            // Browse the list and add to table
            foreach (DataRow row in dt.Rows)
            {
                team = new Team();
                team.TeamId = row["TeamID"].ToString();
                team.Name = row["Name"].ToString();
                team.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                team.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                team.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(team);
            }

            // Return the list
            return list;
        }
        /// <summary>
        /// Get list Team 
        /// </summary>
        /// <param name="includeInactive"></param>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public List<Team> GetAll(bool includeInactive, string departId)
        {
            // Create a command object
            SqlCommand comm = CreateCommand();
            // Call command by store
            comm.CommandText = "TeamGetAll";
            // Add parammeter to store procedure        
            comm.Parameters.AddWithValue("@includeInactive", includeInactive);
            comm.Parameters.AddWithValue("@DepartmentID", departId);
            // Create a data table
            DataTable dt = ExecuteSelectCommand(comm);
            // Get list of directorate
            List<Team> list = new List<Team>();
            Team team = null;

            // Browse the list and add to table
            foreach (DataRow row in dt.Rows)
            {
                team = new Team();
                team.TeamId = row["TeamID"].ToString();
                team.Name = row["Name"].ToString();
                team.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                team.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                team.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(team);
            }

            // Return the list
            return list;
        }
        /// <summary>
        /// Get a Team by Id
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public Team GetById(string teamId)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "TeamGetById";

            // Add parameter to stored procedure
            comm.Parameters.AddWithValue("@TeamID", teamId);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            Team team = null;

            // if table has row
            if (dt.Rows.Count > 0)
            {

                team = new Team();
                team.Name = dt.Rows[0]["Name"].ToString();
                team.ShortDescription = dt.Rows[0]["ShortDescription"].ToString();
                team.Department = dt.Rows[0]["DepartmentID"] == DBNull.Value ? null : new DepartmentDAO().GetById(dt.Rows[0]["DepartmentID"].ToString());
                team.Address = dt.Rows[0]["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(dt.Rows[0]["AddressID"].ToString());
                team.Contact = dt.Rows[0]["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(dt.Rows[0]["ContactID"].ToString());
                team.FullDescription = dt.Rows[0]["FullDescription"].ToString();
                team.Phone = dt.Rows[0]["Phone"].ToString();
                team.Fax = dt.Rows[0]["Fax"].ToString();
                team.Web = dt.Rows[0]["Web"].ToString();
                team.IsActive = bool.Parse(dt.Rows[0]["IsActive"].ToString());
                team.BusinessType = dt.Rows[0]["BusinessType"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(dt.Rows[0]["BusinessType"].ToString());
                team.Email = dt.Rows[0]["Email"].ToString();


            }

            // return true if success fully, otherwise false
            return team;
        }
        /// <summary>
        /// Insert a new Team
        /// </summary>
        /// <param name="team"></param>
        /// <returns></returns>
        public string Insert(Team team)
        {
            string result;

            // Create new command object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "TeamInsert";

            // Add parameters to commnand
            comm.Parameters.AddWithValue("@Name", team.Name);
            comm.Parameters.AddWithValue("@ShortDescription", team.ShortDescription);
            comm.Parameters.AddWithValue("@ContactID", team.Contact == null ? null : team.Contact.ContactId);
            comm.Parameters.AddWithValue("@AddressID", team.Address.AddressId);
            comm.Parameters.AddWithValue("@FullDescription", team.FullDescription);
            comm.Parameters.AddWithValue("@Phone", team.Phone);
            comm.Parameters.AddWithValue("@Fax", team.Fax);
            comm.Parameters.AddWithValue("@Web", team.Web);
            comm.Parameters.AddWithValue("@IsActive", team.IsActive);
            comm.Parameters.AddWithValue("@BusinessType", team.BusinessType.RefId);
            comm.Parameters.AddWithValue("@DepartmentID", team.Department.DepartmentId);
            comm.Parameters.AddWithValue("@Email", team.Email);

            try
            {
                comm.Connection.Open();
                // Execute the stored procedure
                result = comm.ExecuteScalar().ToString();
                comm.Connection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return result;
        }
        /// <summary>
        /// Get a Team by Name
        /// </summary>
        /// <param name="dirName"></param>
        /// <returns></returns>
        public List<Team> GetDirByName(string dirName)
        {
            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "TeamGetByName";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@DirName", dirName);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            List<Team> list = new List<Team>();

            // add each org to list
            foreach (DataRow row in dt.Rows)
            {
                Team team = new Team();
                team.TeamId = row["TeamID"].ToString();
                team.Name = row["Name"].ToString();
                team.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                team.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                team.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(team);
            }

            // return list organization
            return list;
        }
        /// <summary>
        /// Update a Team
        /// </summary>
        /// <param name="team"></param>
        /// <returns></returns>
        public bool Update(Team team)
        {
            int result;

            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command
            comm.CommandText = "TeamUpdate";

            // Add parameters to command
            comm.Parameters.AddWithValue("@TeamID", team.TeamId);
            comm.Parameters.AddWithValue("@Name", team.Name);
            comm.Parameters.AddWithValue("@ShortDescription", team.ShortDescription);
            comm.Parameters.AddWithValue("@ContactID", team.Contact == null ? null : team.Contact.ContactId);
            comm.Parameters.AddWithValue("@AddressID", team.Address == null ? null : team.Address.AddressId);
            comm.Parameters.AddWithValue("@FullDescription", team.FullDescription);
            comm.Parameters.AddWithValue("@Phone", team.Phone);
            comm.Parameters.AddWithValue("@Fax", team.Fax);
            comm.Parameters.AddWithValue("@Web", team.Web);
            comm.Parameters.AddWithValue("@IsActive", team.IsActive);
            comm.Parameters.AddWithValue("@BusinessType", team.BusinessType == null ? null : team.BusinessType.RefId);
            comm.Parameters.AddWithValue("@Email", team.Email);

            try
            {
                // Execute the stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return (result != -1);
        }
        public bool SetActive(string teamId, bool status)
        {
            int result;

            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command
            comm.CommandType = CommandType.Text;
            comm.CommandText = "UPDATE Team SET IsActive = @IsActive WHERE TeamID = @TeamID";
            comm.Parameters.AddWithValue("@TeamID", teamId);
            comm.Parameters.AddWithValue("@IsActive", status);

            try
            {
                // Execute the stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return (result != -1);
        }
    }
}
